package com.api.hrm.cmd.salaryrp.deptsalaryrp;

import com.api.hrm.util.PageUidFactory;
import com.cloudstore.dev.api.util.Util_TableMap;
import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import weaver.common.DateUtil;
import weaver.conn.RecordSet;
import weaver.general.PageIdConst;
import weaver.general.TimeUtil;
import weaver.general.Util;
import weaver.hrm.HrmUserVarify;
import weaver.hrm.User;
import weaver.hrm.finance.SalaryComInfo;
import weaver.systeminfo.SystemEnv;

import java.util.HashMap;
import java.util.Map;

/**
 * 获取按部门汇总的工资报表
 */
public class GetDeptSalaryListCmd extends AbstractCommonCommand<Map<String, Object>> {

    public GetDeptSalaryListCmd(Map<String, Object> params, User user) {
        this.user = user;
        this.params = params;
    }

    @Override
    public BizLogContext getLogContext() {
        return null;
    }

    @Override
    public Map<String, Object> execute(CommandContext commandContext) {
        Map<String, Object> resultMap = new HashMap<String, Object>();

        String deptId = Util.null2String(params.get("deptId"));//部门ID
        String workStatus = Util.null2String(params.get("workstatus"));//工作状态
        String payYearSelect = Util.null2String(params.get("payYearSelect"));//日期控件的选择值
        String payYear = Util.null2String(params.get("payYear"));//年份
        String dateFrom = Util.null2String(params.get("dateFrom"));//月份
        if (!payYearSelect.equals("") && !payYearSelect.equals("0") && !payYearSelect.equals("6")) {
            payYear = TimeUtil.getDateByOption(payYearSelect, "0").substring(0, 4);
            payYear = TimeUtil.getDateByOption(payYearSelect, "1").substring(0, 4);
        }
        String payMonthFrom = payYear + "-01";
        String payMonthTo = payYear + "-12";

        int colNum = 0;//一共有多少列
        String colString = "";//由于列表的列不固定，由此字符串拼凑
        String sumColumnsStr = "";//用于计算每列的总计
        String decimalFormatStr = "";//用于格式化每列的总计数据，否则小数点不可控
        String columnStr = "";//用于拼凑SQL语句
        SalaryComInfo salaryComInfo = new SalaryComInfo();

        String sqlWhere = "";
        if (!payMonthFrom.equals("")) {
            sqlWhere += " and t2.paydate is not null and t2.paydate >='" + payMonthFrom + "' ";
        }
        if (!payMonthTo.equals("")) {
            sqlWhere += " and t2.paydate is not null and t2.paydate <='" + payMonthTo + "' ";
        }
        if (!(workStatus.equals("") || workStatus.equals("9"))) {
            if (workStatus.equals("8")) {
                sqlWhere += " and t3.status in (0,1,2,3) ";
            } else {
                sqlWhere += " and t3.status =" + workStatus + " ";
            }
        }
        if (!deptId.equals("")) {
            sqlWhere += " and t1.departmentid = " + deptId;
        }
        if("0".equals( dateFrom )){
            String curMonth = DateUtil.getCurrentDate().substring( 0,7 );
            sqlWhere = sqlWhere + " and t2.paydate is not null and t2.paydate ='" + curMonth + "' ";
        }else {
            String start = Util.null2String(params.get("start"));
            String end = Util.null2String(params.get("end"));
            if (!start.equals("")) {
                sqlWhere = sqlWhere + " and t2.paydate is not null and t2.paydate >='" + start + "' ";
            }

            if (!end.equals("")) {
                sqlWhere = sqlWhere + " and t2.paydate is not null and t2.paydate <='" + end + "' ";
            }
        }

        String sql = "SELECT DISTINCT t1.itemid FROM HrmSalaryPaydetail t1, HrmSalaryPay t2,HrmResource t3 WHERE t1.payid = t2.id and t1.hrmid = t3.id " + sqlWhere;
        RecordSet recordSet = new RecordSet();
        recordSet.executeQuery(sql);

        colNum = recordSet.getCounts();
        colString += "<col width=\"10%\" text=\"" + SystemEnv.getHtmlLabelName(124, user.getLanguage()) + "\" column=\"departmentid\" orderkey=\"departmentid\" transmethod=\"weaver.hrm.company.DepartmentComInfo.getDepartmentname\" " +
                " display=\"true\"  showType=\"0\" isInputCol=\"false\" isPrimarykey=\"false\" />";
        while (recordSet.next()) {
            String itemId = Util.null2String(recordSet.getString("itemId"));
            String itemName = Util.toScreen(salaryComInfo.getSalaryname(itemId.indexOf("_") > -1 ? itemId.substring(0, itemId.indexOf("_")) : itemId), user.getLanguage());

            colString += "<col width=\"10%\" text=\"" + itemName + "\" column=\"itemId" + itemId + "\" />";
            sumColumnsStr += ",itemId" + itemId;
            decimalFormatStr += "|%.2f";
            if (recordSet.getDBType().equalsIgnoreCase("oracle")) {
                columnStr += ",'" + itemId + "' itemId" + itemId + "";
            } else if (recordSet.getDBType().equalsIgnoreCase("mysql")) {
                columnStr += ",Max(case a.itemid when '" + itemId + "' then IFNULL(allsalary,0) else 0 end ) 'itemId" + itemId + "' ";
            } else {
                columnStr += ",[itemId" + itemId + "]";
            }
        }
        sumColumnsStr = sumColumnsStr.length() > 0 ? sumColumnsStr.substring(1) : "";
        decimalFormatStr = decimalFormatStr.length() > 0 ? decimalFormatStr.substring(1) : "";
        columnStr = columnStr.length() > 0 ? columnStr.substring(1) : "";

        if (colNum == 0) {
            resultMap.put("hasDatas", false);
            return resultMap;
        }

        String backFields = " * ";
        String sqlFrom = "";
        if (recordSet.getDBType().equalsIgnoreCase("oracle")) {
            sqlFrom = " from (select t1.departmentid, t1.itemid, sum(salary) as allsalary " +
                    " from HrmSalaryPaydetail t1, HrmSalaryPay t2, HrmResource t3 where t1.payid = t2.id and t1.hrmid = t3.id " + sqlWhere + " group by t1.departmentid, t1.itemid order by t1.departmentid) " +
                    " pivot(sum(allsalary) for itemid in (" + columnStr + ") )";
        } else if (recordSet.getDBType().equalsIgnoreCase("mysql")) {
            sqlFrom = " from (select a.departmentid," + columnStr +
                    " from (select t1.departmentid, t1.itemid, sum(salary) as allsalary " +
                    " from HrmSalaryPaydetail t1, HrmSalaryPay t2, HrmResource t3 where t1.payid = t2.id and t1.hrmid = t3.id " + sqlWhere + " group by t1.departmentid, t1.itemid) a" +
                    " group by a.departmentid) t";
        } else {
            sqlFrom = " from (select t1.departmentid, 'itemId'+t1.itemid itemId, sum(salary) as allsalary " +
                    " from HrmSalaryPaydetail t1, HrmSalaryPay t2, HrmResource t3 where t1.payid = t2.id and t1.hrmid = t3.id " + sqlWhere + " group by t1.departmentid, t1.itemid ) AS C " +
                    " pivot(sum(allsalary) for itemid in (" + columnStr + ") ) AS T";
        }
        String operateString = "";
        operateString = "<operates width=\"10%\">";
        operateString += " <popedom transmethod=\"weaver.hrm.common.SplitPageTagOperate.getBasicOperate\" otherpara=\"" + true + "\"></popedom> ";
        operateString += "     <operate href=\"javascript:openDialog();\" text=\"" + SystemEnv.getHtmlLabelName(22045, user.getLanguage()) + "\" index=\"0\" />";
        operateString += "</operates>";
        String pageUid = PageUidFactory.getHrmPageUid("DeptSalaryRpList");
        String tableString = "";
        tableString = " <table pageUid=\"" + pageUid + "\" pageId=\"" + PageIdConst.HRM_DeptSalaryRpList + "\" pagesize=\"" + PageIdConst.getPageSize(PageIdConst.HRM_DeptSalaryRpList, user.getUID(), PageIdConst.HRM) + "\" tabletype=\"none\">" +
                " <sql backfields=\"" + backFields + "\" sqlform=\"" + Util.toHtmlForSplitPage(sqlFrom) + "\" sqlwhere=\"\"  sqlorderby=\"\"  sqlprimarykey=\"departmentid\" sqlsortway=\"Asc\" sqlisdistinct=\"false\" sumColumns=\"" + sumColumnsStr + "\" decimalFormat=\"" + decimalFormatStr + "\" />" +
                operateString +
                "	<head>" + colString +
                "	</head>" +
                " </table>";
        String sessionKey = pageUid + "_" + Util.getEncrypt(Util.getRandom());
        Util_TableMap.setVal(sessionKey, tableString);
        resultMap.put("sessionkey", sessionKey);
        resultMap.put("colNum", colNum + 1);
        return resultMap;
    }
}
